package com.elm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.elm.dao.UserDao;
import com.elm.model.User;
import com.elm.util.JDBCUtil;

public class UserDaoImpl implements UserDao{

	public boolean validateUname(String uname) {
		boolean flag = false;
		PreparedStatement pst = null;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select userid from user where username=?");
		try {
			 pst = con.prepareStatement(sql.toString());
			 pst.setString(1, uname);
			 ResultSet rs =pst.executeQuery();
			 if(rs.next()){
				 flag=true;
			 }
			 JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean validateUphone(String uphone) {
		boolean flag = false;
		PreparedStatement pst = null;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select userid from user where userphone=?");
		try {
			 pst = con.prepareStatement(sql.toString());
			 pst.setString(1, uphone);
			 ResultSet rs =pst.executeQuery();
			 if(rs.next()){
				 flag=true;
			 }
			 JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean addUser(User u) {
		// TODO Auto-generated method stub
		boolean  flag = false;
		PreparedStatement pst = null;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("insert into user (username,userpsw,userphone,useraddr) values(?,?,?,?)");
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, u.getUsername());
			pst.setString(2, u.getUserpsw());
			pst.setString(3, u.getUserphone());
			pst.setString(4, u.getUseraddr());
			int i = pst.executeUpdate();
			if(i>0){
				flag = true;
			}
			 JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public User validateUser(User u) {
		boolean  flag = false;
		User user = new User();
		PreparedStatement pst = null;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from user where (username=? or userphone =?) and userpsw =?");
		try {
			
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, u.getUsername());
			pst.setString(2,u.getUsername());
			pst.setString(3, u.getUserpsw());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setUserpsw(rs.getString("userpsw"));
				user.setUserphone(rs.getString("userphone"));
				user.setUseraddr(rs.getString("useraddr"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return user;
	}
	/**
	 * 查询当前模糊查询条件下有几条数据
	 */
	public int getUsersCountByName(String searchname) {
		// TODO Auto-generated method stub
		int  scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(userid) scount from user where username like ? and isdel='0' ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				scount =rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	/**
	 * 通过用户名查找用户
	 */
	@Override
	public List<User> FindUserByName(int start,int pageSize,String searchname) {
		// TODO Auto-generated method stub
		List<User> us = new ArrayList<User>();	
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select userid,username,userphone,useraddr,isdel from user where username like ? and isdel='0' ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				User u = new User();
				u.setUserid(rs.getInt("userid"));
				u.setUsername(rs.getString("username"));
				u.setUserphone(rs.getString("userphone"));
				u.setUseraddr(rs.getString("useraddr"));
				u.setIsdel(rs.getInt("isdel"));
				us.add(u);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return us;
	}
/**
 * 查询总共有多少用户
 */
	@Override
	public int getUsersCount() {
		// TODO Auto-generated method stub
		int scount=0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(userid) scount from user where isdel='0' ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}
	/**
	 * 查询所有用户的信息
	 */
	@Override
	public List<User> FindAllUsers(int start,int pageSize) {
		// TODO Auto-generated method stub
		List<User> allus = new ArrayList<User>(); 
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select userid,username,userphone,useraddr,isdel from user where isdel='0' ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				User s = new User();
				s.setUserid(rs.getInt("userid"));
				s.setUsername(rs.getString("username"));
				s.setUserphone(rs.getString("userphone"));
				s.setUseraddr(rs.getString("useraddr"));
				s.setIsdel(rs.getInt("isdel"));
				allus.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allus;
	}

	/**
	 * 删除用户的信息
	 */
	@Override
	public boolean deleteuser(int usid) {
		// TODO Auto-generated method stub
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update user set isdel=1 where userid=?");
		try {
			PreparedStatement pst =con.prepareStatement(sql.toString());
			pst.setInt(1, usid);
			int j = pst.executeUpdate();
			while(j>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 查询全部过往用户
	 */
	public List<User> FindOldUsers(int start, int pageSize) {
		List<User> oldus = new ArrayList<User>(); 
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select userid,username,userphone,useraddr,isdel from user where isdel='1' ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				User s = new User();
				s.setUserid(rs.getInt("userid"));
				s.setUsername(rs.getString("username"));
				s.setUserphone(rs.getString("userphone"));
				s.setUseraddr(rs.getString("useraddr"));
				s.setIsdel(rs.getInt("isdel"));
				oldus.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return oldus;
	}
/**
 * 查询总共有多少个过往用户
 */
	@Override
	public int getOldUsersCount() {
		int olducount=0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(userid) olducount from user where isdel='1' ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				olducount = rs.getInt("olducount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return olducount;
	}
	/**
	 * 通过名字查找过往用户
	 */
	public List<User> FindOldUserByName(int start, int pageSize, String searchname) {
		List<User> oldu = new ArrayList<User>();	
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select userid,username,userphone,useraddr,isdel from user where username like ? and isdel='1' ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				User u = new User();
				u.setUserid(rs.getInt("userid"));
				u.setUsername(rs.getString("username"));
				u.setUserphone(rs.getString("userphone"));
				u.setUseraddr(rs.getString("useraddr"));
				u.setIsdel(rs.getInt("isdel"));
				oldu.add(u);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return oldu;
	}
	/**
	 * 模糊查询条件下总共有多少个过往用户
	 */
	@Override
	public int getOldUserCountByName(String searchname) {
		int  olducount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(userid) olducount from user where username like ? and isdel='1' ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				olducount =rs.getInt("olducount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return olducount;
	}

	/**
	 * 通过id来还原用户
	 */
	public boolean restoreuser(int usid) {
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update user set isdel=0 where userid=?");
		try {
			PreparedStatement pst =con.prepareStatement(sql.toString());
			pst.setInt(1, usid);
			int j = pst.executeUpdate();
			while(j>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	public int updateUserInfobyUserid(String newpsw, String newaddr, String newphone, int userid) {
		int i = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update user set userpsw = ?,useraddr = ?, userphone = ? where userid = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, newpsw);
			pst.setString(2, newaddr);
			pst.setString(3, newphone);
			pst.setInt(4, userid);
			i = pst.executeUpdate();
			if(i > 0) {
				i=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

}


